/**
 * Auto gen via dbpojo-gen.
 */
package com.us.usws.dao;

import java.util.List;

import net.iwsh.core.db.DBExecutor;
import net.iwsh.core.db.ext.DialectSyntaxParser;
import net.iwsh.core.db.ext.SQLWithParams;
import net.iwsh.core.utils.IDGenerator;

import com.us.usws.model.domain.Meeting;
import com.us.usws.model.flat.DOMeeting;

/**
 * DAO class
 * @author robot
 */
public class MeetingDAO {

	/**
	 * Query SQLs
	 */
	private static final String SQL_GET_TOTAL_COUNT = "select count(*) from hv_meeting";

	private static final String SQL_GET_ALL_MEETINGS = "select * from hv_meeting order by room_id";

	private static final String SQL_GET_MEETING_BY_PK = "select * from hv_meeting where uid = ?";

	private static final String SQL_GET_MEETINGS_BY_CHANNEL = "select * from hv_meeting where channel_uid=? order by start_date desc";
	private static final String SQL_GET_MEETINGS_BY_CHANNEL_SEQ_NUM = "select m.* from hv_meeting m inner join hv_channel c on c.uid=m.channel_uid where m.channel_uid=? and c.seq_num=?";

	private static final String SQL_GET_MEETINGS_BY_PRESENTER_AND_TYPE = "select * from hv_meeting where presenter_uid=? and type_dsid=?";
	private static final String SQL_GET_MEETINGS_BY_USER_ANY = "select * from hv_meeting m where m.presenter_uid=? or m.create_by_uid=? or exists (select 1 from hv_meeting_attendee a where a.meeting_uid=m.uid and a.attendee_uid=?)";

	/**
	 * CUR SQLs
	 */
	private static final String SQL_INSERT_MEETING = "insert into hv_meeting(uid,title,room_id,channel_uid,content,start_date,end_date,presenter_uid,type_dsid,server_type_dsid,status_dsid,picture,video_type_dsid,create_by_uid,create_date,last_upd_by_uid,last_up_date) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

	private static final String SQL_UPDATE_MEETING = "update hv_meeting set title=?, room_id=?, channel_uid=?, content=?, start_date=?, end_date=?, presenter_uid=?, type_dsid=?, server_type_dsid=?, status_dsid=?, picture=?, video_type_dsid=?, create_by_uid=?, create_date=?, last_upd_by_uid=?, last_up_date=? where uid=?";

	private static final String SQL_DEL_MEETING = "delete from hv_meeting where uid=?";

	private static final String SQL_GET_THEME_MEETINGS = "select m.* from hv_meeting m inner join box_theme_meeting_link l on l.meeting_uid=m.uid inner join box_theme t on t.uid=l.theme_uid where t.uid=?";

	/**
	 * DBExeutor
	 */
	private DBExecutor dbe = new DBExecutor();

	/**
	 * Get Count.
	 */
	public int getTotalCount() {
		return dbe.getCount(SQL_GET_TOTAL_COUNT);
	}

	/**
	 * Get all list.
	 */
	public List<Meeting> getAllMeetings() {
		return dbe.list(SQL_GET_ALL_MEETINGS, Meeting.class);
	}

	/**
	 * Get all list by pagination.
	 */
	public List<Meeting> getMeetings(int start, int end) {
		SQLWithParams swp = DialectSyntaxParser.getParser().getPaginationSQLWithParams(SQL_GET_ALL_MEETINGS, null, start, end);
		return dbe.list(swp.getSql(), Meeting.class, swp.getParams());
	}

	/**
	 * Get Meeting by primary key.
	 */
	public Meeting getMeeting(String uid) {
		Meeting m = dbe.load(SQL_GET_MEETING_BY_PK, Meeting.class, uid);
		return m;
	}

	/**
	 * Insert one row.
	 */
	public String insertMeeting(DOMeeting meeting) {
		String uid = IDGenerator.getInstance().genUID();
		meeting.setUid(uid);
		dbe.execute(SQL_INSERT_MEETING, meeting.getUid(), meeting.getTitle(), meeting.getRoomId(), meeting.getChannelUid(),
				meeting.getContent(), meeting.getStartDate(), meeting.getEndDate(), meeting.getPresenterUid(), meeting.getTypeDsid(),
				meeting.getServerTypeDsid(), meeting.getStatusDsid(), meeting.getPicture(), meeting.getVideoTypeDsid(),
				meeting.getCreateByUid(), meeting.getCreateDate(), meeting.getLastUpdByUid(), meeting.getLastUpDate());
		return uid;
	}

	/**
	 * Update by PK.
	 */
	public void updateMeeting(DOMeeting meeting) {
		dbe.execute(SQL_UPDATE_MEETING, meeting.getTitle(), meeting.getRoomId(), meeting.getChannelUid(), meeting.getContent(),
				meeting.getStartDate(), meeting.getEndDate(), meeting.getPresenterUid(), meeting.getTypeDsid(),
				meeting.getServerTypeDsid(), meeting.getStatusDsid(), meeting.getPicture(), meeting.getVideoTypeDsid(),
				meeting.getCreateByUid(), meeting.getCreateDate(), meeting.getLastUpdByUid(), meeting.getLastUpDate(), meeting.getUid());
	}

	/**
	 * Delete row by PK.
	 */
	public void deleteMeeting(String uid) {
		dbe.execute(SQL_DEL_MEETING, uid);
	}

	/**
	 * get meetings by channel
	 * @param channelUid
	 * @return
	 */
	public int getMeetingCountByChannle(String channelUid) {
		String sql = DialectSyntaxParser.getParser().getCountSQL(SQL_GET_MEETINGS_BY_CHANNEL);
		return dbe.getCount(sql, channelUid);
	}

	/**
	 * get meetings by channel, with pagination
	 * @param channelUid
	 * @param start
	 * @param size
	 * @return
	 */
	public List<Meeting> getMeetingssByChannel(String channelUid, int start, int size) {
		SQLWithParams swp = DialectSyntaxParser.getParser().getPaginationSQLWithParams(SQL_GET_MEETINGS_BY_CHANNEL,
				new Object[] { channelUid }, start, size);
		return dbe.list(swp.getSql(), Meeting.class, swp.getParams());
	}

	/**
	 * get meetings count by presenter and meeting type.
	 * @param userUid
	 * @param meetingType
	 * @return
	 */
	public int getMeetingCountByPresenterAndType(String userUid, int meetingType) {
		String sql = DialectSyntaxParser.getParser().getCountSQL(SQL_GET_MEETINGS_BY_PRESENTER_AND_TYPE);
		return dbe.getCount(sql, userUid, meetingType);
	}

	/**
	 * get meetings by presenter and meeting type
	 * @param userUid
	 * @param meetingtype
	 * @param start
	 * @param pagesize
	 * @return
	 */
	public List<Meeting> getMeetingsByPresenterAndType(String userUid, int meetingtype, int start, int pagesize) {
		SQLWithParams swp = DialectSyntaxParser.getParser().getPaginationSQLWithParams(SQL_GET_MEETINGS_BY_PRESENTER_AND_TYPE,
				new Object[] { userUid, meetingtype }, start, pagesize);
		return dbe.list(swp.getSql(), Meeting.class, swp.getParams());
	}

	/**
	 * get meeting count by user any
	 * @param uid
	 * @return
	 */
	public int getMeetingCountByUsersAny(String uid) {
		String sql = DialectSyntaxParser.getParser().getCountSQL(SQL_GET_MEETINGS_BY_USER_ANY);
		return dbe.getCount(sql, uid, uid, uid);
	}

	/**
	 * get meetings by user any
	 * @param uid
	 * @param start
	 * @param pageSize
	 * @return
	 */
	public List<Meeting> getMeetingsByUsersAny(String uid, int start, int pageSize) {
		SQLWithParams swp = DialectSyntaxParser.getParser().getPaginationSQLWithParams(SQL_GET_MEETINGS_BY_USER_ANY,
				new Object[] { uid, uid, uid }, start, pageSize);
		return dbe.list(swp.getSql(), Meeting.class, swp.getParams());
	}

	public List<Meeting> getMeetingsByChannelAndSeqNum(String channelUid, int seqNum, int start, int pageSize) {
		SQLWithParams swp = DialectSyntaxParser.getParser().getPaginationSQLWithParams(SQL_GET_MEETINGS_BY_CHANNEL_SEQ_NUM,
				new Object[] { channelUid, seqNum }, start, pageSize);
		return dbe.list(swp.getSql(), Meeting.class, swp.getParams());
	}

	public int getMeetingCountByChannelAndSeqNum(String channelUid, int seqNum) {
		String sql = DialectSyntaxParser.getParser().getCountSQL(SQL_GET_MEETINGS_BY_CHANNEL_SEQ_NUM);
		return dbe.getCount(sql, channelUid, seqNum);
	}

	public List<Meeting> getThemeVideos(String themeUid) {
		return dbe.list(SQL_GET_THEME_MEETINGS, Meeting.class, themeUid);
	}
}